USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[KonseiResult] スクリプト日付: 2014/05/12 13:37:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KonseiResult]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KonseiResult];
GO

CREATE PROCEDURE [dbo].[KonseiResult]
	@TAIKAINO int,
	@KYOGINO int,
	@first int 
AS

SELECT 
	A.KYOGISYANO, 
	dbo.Jyuni(A.JYUNI,D.OPENFLG) AS JYUNI, 
	CASE E.SYUKKETU WHEN 2 THEN '' ELSE A.TOKUTEN END AS TOKUTEN, 
	dbo.CommentCodeConvert(A.RCOMMENT, E.SYUKKETU) AS COMMENTMEI,
	A.RCOMMENT,
	B.KYOGISYAMEI, 
	B.KYOGISYAKANA, 
	B.NUMBER1, 
	B.NUMBER2, 
	C.SYOZOKUMEI, 
	B.KOJINSYOZOKUTIMEI, 
	B.GAKUNEN, 
	B.SEINEN, 
	D.OPENFLG, 
	A.TOKUTENFLG, 
	D.KIROKU,
	E.SYUKKETU
FROM 
	TBL_KONSEI_SYUKEI AS A 
LEFT JOIN 
	TBL_KYOGISYA AS B 
ON 
	A.TAIKAINO = B.TAIKAINO 
	AND A.KYOGISYANO = B.KYOGISYANO 
LEFT JOIN 
	TBL_SYOZOKU AS C 
ON 
	B.TAIKAINO = C.TAIKAINO 
	AND B.SYOZOKUNO1 = C.SYOZOKUNO 
LEFT JOIN 
	TBL_SANKAKYOGI AS D 
ON 
	A.TAIKAINO = D.TAIKAINO 
	AND A.KYOGINO = D.KYOGINO 
	AND A.KYOGISYANO = D.KYOGISYANO 
LEFT OUTER JOIN(
	SELECT * FROM TBL_KONSEI_KOBETU AS E WHERE E.SYUMOKUNO = @first)AS E
ON
	A.TAIKAINO = E.TAIKAINO
	AND A.KYOGINO = E.KYOGINO
	AND A.KYOGISYANO = E.KYOGISYANO

WHERE 
	A.TAIKAINO = @TAIKAINO 
	AND A.KYOGINO = @KYOGINO
	

ORDER BY
	CASE E.SYUKKETU WHEN 2 THEN 2 ELSE 1 END,
	CASE A.TOKUTEN WHEN 0 THEN 2 ELSE 1 END,
	A.JYUNI,
	A.TOKUTEN DESC